﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Ylh.DBUtility;
using Ylh.IDAL;
using Ylh.Model;
using System.Data;
using System.Data.SqlClient;

namespace Ylh.SQLServerDAL
{
    public class DepartmentDAL : IDepartment
    {
        /// <summary>
        /// 插入一条数据,并且放回ID号码
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="model">TbDepartment对象</param>
        /// <returns></returns>
        public int InsertDepartment(string ConStr, TbDepartment model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TbDepartment(");
            strSql.Append("DepartmentName,Slogan,TeamName,Remark,StaffID,IsServer)");
            strSql.Append(" values (");
            strSql.Append("@DepartmentName,@Slogan,@TeamName,@Remark,@StaffID,@IsServer)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] pre = {
					new SqlParameter("@DepartmentName", SqlDbType.VarChar,30),
					new SqlParameter("@Slogan", SqlDbType.VarChar,100),
					new SqlParameter("@TeamName", SqlDbType.VarChar,30),
					new SqlParameter("@Remark", SqlDbType.NText),
					new SqlParameter("@StaffID", SqlDbType.Int,4),
                                 new SqlParameter("@IsServer", SqlDbType.Bit,1)};
            pre[0].Value = model.DepartmentName;
            pre[1].Value = model.Slogan;
            pre[2].Value = model.TeamName;
            pre[3].Value = model.Remark;
            pre[4].Value = model.StaffID;
            pre[5].Value = model.IsServer;
            DataTable dt = SqlHelper.ExcuteQuery(ConStr, strSql.ToString(), pre, CommandType.Text);
            return Convert.ToInt32(dt.Rows[0][0]);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="model">TbDepartment对象</param>
        /// <returns></returns>
        public bool UpdateDepartment(string ConStr, TbDepartment model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update TbDepartment set ");
            strSql.Append("DepartmentName=@DepartmentName,");
            strSql.Append("Slogan=@Slogan,");
            strSql.Append("TeamName=@TeamName,");
            strSql.Append("Remark=@Remark,");
            strSql.Append("StaffID=@StaffID,");
            strSql.Append("IsServer=@IsServer");
            strSql.Append(" where DepartmentID=@DepartmentID ");
            SqlParameter[] pre = {
					new SqlParameter("@DepartmentID", SqlDbType.Int,4),
					new SqlParameter("@DepartmentName", SqlDbType.VarChar,30),
					new SqlParameter("@Slogan", SqlDbType.VarChar,100),
					new SqlParameter("@TeamName", SqlDbType.VarChar,30),
					new SqlParameter("@Remark", SqlDbType.NText),
					new SqlParameter("@StaffID", SqlDbType.Int,4),
                      new SqlParameter("@IsServer", SqlDbType.Bit,1)
                                 };
            pre[0].Value = model.DepartmentID;
            pre[1].Value = model.DepartmentName;
            pre[2].Value = model.Slogan;
            pre[3].Value = model.TeamName;
            pre[4].Value = model.Remark;
            pre[5].Value = model.StaffID;
            pre[6].Value = model.IsServer;
            int i = SqlHelper.ExecuteNonQuery(ConStr, strSql.ToString(), pre, CommandType.Text);

            if (i > 0) return true;
            return false;
        }


        /// <summary>
        /// 判断部门名称是否存在
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="DeparName">部门名称</param>
        /// <returns></returns>
        public bool IsDeParNameExists(string ConStr, string DeparName)
        {
            string sql = @"SELECT DepartmentID FROM TbDepartment where DepartmentName =@DepartmentName";

            SqlParameter[] pre = new SqlParameter[] 
            {
              new SqlParameter ("@DepartmentName",DeparName)
             };
            DataTable dt = SqlHelper.ExcuteQuery(ConStr, sql, pre, CommandType.Text);
            if (dt.Rows.Count > 0) return true;
            return false;
        }

        /// <summary>
        /// 判断部门下面是否存在员工
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="DepartmentID">部门编号</param>
        /// <returns></returns>
        public bool IsdeparmentHseStaff(string ConStr, string DepartmentID)
        {
            string sql = @"SELECT * FROM TbStaff where DepartmentID=@DepartmentID";
            SqlParameter[] pre = new SqlParameter[] 
            {
              new SqlParameter ("@DepartmentID",DepartmentID)
             };

            DataTable dt = SqlHelper.ExcuteQuery(ConStr, sql, pre, CommandType.Text);
            if (dt.Rows.Count > 0) return true;
            return false;
        }
        /// <summary>
        /// 根据部门编号删除部门信息
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="DepartmentID">部门编号</param>
        /// <returns></returns>
        public bool DeleteDepartmentInfo(string ConStr, string DepartmentID)
        {
            string sql = @"DELETE TbDepartment where DepartmentID=@DepartmentID";
            SqlParameter[] pre = new SqlParameter[] 
            {
              new SqlParameter ("@DepartmentID",DepartmentID)
             };

            int i = SqlHelper.ExecuteNonQuery(ConStr, sql, pre, CommandType.Text);
            if (i > 0) return true;
            return false;
        }

        /// <summary>
        /// 获取部门信息
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="DepartmentID">部门编号</param>
        /// <returns></returns>
        public DataTable GetDepartMentInfo(string ConStr, decimal DepartmentID)
        {
            string sql = "SELECT TbDepartment.* ,TbStaff.StaffName FROM TbDepartment LEFT JOIN TbStaff on TbDepartment.StaffID=TbStaff.StaffID where 1=1";
            if (DepartmentID != -1)
            {
                sql += "  and  TbDepartment.DepartmentID=" + DepartmentID;
            }
            return SqlHelper.ExcuteQuery(ConStr, sql, CommandType.Text);
        }


     /// <summary>
     /// 获取服务部门信息
     /// 两个条件同时作用
     /// </summary>
     /// <param name="ConStr"></param>
     /// <param name="DepartmentID">部门ID,如果为-1则获取所有服务部门的信息</param>
     /// <param name="IsServer">
     /// true 获取服务部门
     /// false 获取非服务部门
     /// null 获取所有部门信息
     /// </param>
     /// <returns></returns>
        public DataTable GetServerDepartMentInfo(string ConStr, decimal DepartmentID, bool? IsServer)
        {
            string sql = "SELECT TbDepartment.* ,TbStaff.StaffName FROM TbDepartment LEFT JOIN TbStaff on TbDepartment.StaffID=TbStaff.StaffID where 1=1";

            if (IsServer != null)
            {
                if (Convert.ToBoolean(IsServer))
                {
                    sql += "  and  TbDepartment.IsServer<>0 ";
                }
                else
                {
                    sql += "  and  TbDepartment.IsServer=0 ";
                }

                if (DepartmentID != -1)
                {
                    sql += "  and  TbDepartment.DepartmentID=" + DepartmentID;
                }
            }
            else
            {
                if (DepartmentID != -1)
                {
                    sql += "  and  TbDepartment.DepartmentID=" + DepartmentID;
                }
            }


            return SqlHelper.ExcuteQuery(ConStr, sql, CommandType.Text);
        }


        /// <summary>
        /// 设置部门的负责人
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="staffID">负责人ID</param>
        /// <param name="DeparmentID">部门编号</param>
        /// <returns></returns>
        public bool SetDeparmentHead(string ConStr, string staffID, string DeparmentID)
        {
            string sql = "UPDATE TbDepartment SET StaffID=" + staffID + " WHERE DepartmentID=" + DeparmentID;
            int i = SqlHelper.ExecuteNonQuery(ConStr, sql, CommandType.Text);
            if (i > 0) return true;
            return false;
        }






    }
}
